import re
from functools import reduce
from operator import or_
from django.shortcuts import render,get_object_or_404
from django.http.response import HttpResponse
import pandas as pd
from django.http import JsonResponse
from django.views.decorators.csrf import csrf_exempt
from ..models import TiaojixinxiD1,TiaojixinxiD2,TiaojixinxiD3
from django.http import JsonResponse
from django.db.models import Q
from ..models import TiaojixinxiD1  # 假设你的模型名是TiaojixinxiD2
from ..models import CareerStaff_D2
def index_tiaojisystem(request):
    return render(request,'index.html')

def index_searchinfo(request):
    return render(request,'searchinfo.html')
def index_searchinfodemo2(request):
    return render(request,'searchinfodemo2.html')
def index_searchinfodemo3(request):
    return render(request,'searchinfodemo3.html')

def index_searchinfodemo4(request):
    return render(request,'searchinfodemo4.html')
def index_careerstaffdemo1(request):
    return render(request,'carerrstaffinfo.html')

@csrf_exempt
def upload_excel(request):
    if request.method == 'POST' and request.FILES.get('file'):
        print(1111)
        excel_file = request.FILES['file']
        # 获取dname的值
        dname = request.POST.get('dname')
        print(f"dname:{dname}")
        try:
            df = pd.read_excel(excel_file)  # 读取Excel文件
            # 将整个 DataFrame 转换为字符串
            df = df.astype(str)
            if dname == 'd1':# 表示自制的数据集-TiaojixinxiD1
                records = []
                for _, row in df.iterrows():
                    baokaozhuanyecode_value=row['报考专业']
                    tiaojizhuanyecode_value = row['专业代码']
                    chushiscore_value = row['初试成绩']
                    try:
                        baokaozhuanyecode=str(baokaozhuanyecode_value)
                        baokaozhuanyecode=float(baokaozhuanyecode)
                        baokaozhuanyecode=int(baokaozhuanyecode)
                        baokaozhuanyecode = str(baokaozhuanyecode).zfill(6)
                    except ValueError:
                        print(f"baokaozhuanyecode_value不是数字:{baokaozhuanyecode_value}")

                    tiaojizhuanyecode = str(tiaojizhuanyecode_value).zfill(6)

                    # 判断chushiscore_value能否转成int,不能则设置为null表示有这个记录但是不知道具体分数
                    try:
                        chushiscore = int(float(chushiscore_value))
                    except ValueError:
                        chushiscore = None
                    records.append(TiaojixinxiD1
                                    (name=row['姓名'], tiaojischool=row['录取学校'],tiaojixueyuan=row['学院'],
                                     tiaojizhuanyecode=tiaojizhuanyecode,tiaojizhuanyename=row['专业名称'],
                                     chushiscore=chushiscore,yizhiyuanschool=row['报考学校'],
                                     baokaozhuanyecode=baokaozhuanyecode,yearinfo='2024',
                                     shuxuename="数学二",yingyuname="英语二",zhuanshuoorxueshuo="专硕"
                                     )
                                   )
                batch_size = 500
                for i in range(0, len(records), batch_size):
                    print(f"正在保存第{i // batch_size + 1}批数据")
                    TiaojixinxiD1.objects.bulk_create(records[i:i + batch_size])
                return JsonResponse({'message': '数据上传成功！'}, status=200)
            elif dname == 'd2':# 咸鱼的数据集-TiaojixinxiD2
                records = []
                for _, row in df.iterrows():
                    # 判断chushiscore_value能否转成int,不能则设置为null表示有这个记录但是不知道具体分数
                    chushiscore_value = row['chushiscore']
                    try:
                        chushiscore = int(chushiscore_value)
                    except ValueError:
                        chushiscore = None
                    records.append(TiaojixinxiD2
                                   (tiaojischool=row['tiaojischool'], tiaojixueyuan=row['tiaojixueyuan'],
                                    tiaojizhuanyecode=row['tiaojizhuanyecode'],
                                    tiaojixuekecode=row['tiaojixuekecode'],
                                    tiaojizhuaunyename=row['tiaojizhuaunyename'],
                                    chushiscore=chushiscore,
                                    yizhiyuanschool=row['yizhiyuanschool'], yearinfo='2024'
                                    ))
                batch_size = 500
                for i in range(0, len(records), batch_size):
                    print(f"正在保存第{i // batch_size + 1}批数据")
                    TiaojixinxiD2.objects.bulk_create(records[i:i + batch_size])
                return JsonResponse({'message': '数据上传成功！'}, status=200)
            elif dname == 'd3':  # 咸鱼的数据集-TiaojixinxiD3
                records = []
                for _, row in df.iterrows():
                    chushiscore_value = row.get('初试总分', None)  # 避免 KeyError
                    try:
                        chushiscore = int(float(chushiscore_value)) if pd.notna(chushiscore_value) else None
                    except ValueError:
                        chushiscore = None
                    try:
                        temp = row.get('专业', '')
                        if isinstance(temp, str):
                            match = re.match(r'\((\d+)\)\s*(.+)', temp.strip())
                            if match:
                                tiaojizhuanyecode = match.group(1)
                                tiaojizhuanye = match.group(2)
                            else:
                                tiaojizhuanyecode = None
                                tiaojizhuanye = temp if temp else None
                        else:
                            tiaojizhuanyecode = None
                            tiaojizhuanye = None
                    except Exception as e:
                        tiaojizhuanyecode = None
                        tiaojizhuanye = None
                    records.append(TiaojixinxiD3
                                   (
                                    tiaojischool=row['学校'],
                                    tiaojiaddress=row['地区'],
                                    tiaojidangci=row['院校类别'],
                                    tiaojixueyuan=row['所属学院'],
                                    tiaojizhuanye=tiaojizhuanye,
                                    tiaojizhuanyecode=tiaojizhuanyecode,
                                    xuexixingshi=row['学习形式'],
                                    chushiscore=chushiscore, yizhiyuanschool=row['一志愿报考院校'],
                                    yihziyuancode='12530X', yearinfo='2024'
                                    )
                                   )
                batch_size = 100
                try:
                    for i in range(0, len(records), batch_size):
                        print(f"正在保存第{i // batch_size + 1}批数据")
                        TiaojixinxiD3.objects.bulk_create(records[i:i + batch_size], ignore_conflicts=True)
                except Exception as e:
                        return JsonResponse({'message': f'数据上传失败！{str(e)}'}, status=400)
                return JsonResponse({'message': '数据上传成功！'}, status=200)
            elif dname=='d4':  # 事业编考试ycz
                records = []
                for _, row in df.iterrows():
                    records.append(CareerStaff_D2
                        (
                        zhuguanname=row['主管部门'],
                        danweiname=row['名称'],
                        danweicode=row['单位代码'],
                        jingfeilaiyuan=row['经费来源'],
                        gangweiname=row['岗位名称'],
                        gangweicode=row['岗位代码'],
                        gangweileibie=row['岗位类别'],
                        gangweimiaoshu=row['岗位描述'],
                        nizhaorenshu=row['拟招聘人数'],
                        kaikaobili=row['开考比例'],
                        kaochatijianbili=row['考察体检比例'],
                        xueli=row['学历'],
                        zhuanye=row['专业'],
                        zhaopinduixiang=row['招聘对象'],
                        qitatiaojian=row['其他条件'],
                        zhaopinbumenkaoshibili=row['招聘部门'],
                        qitashuoming=row['其他说明'],
                        zhengcelianxi=row['咨询联系方式'],
                    )
                    )
                batch_size = 100
                try:
                    for i in range(0, len(records), batch_size):
                        print(f"正在保存第{i // batch_size + 1}批数据")
                        CareerStaff_D2.objects.bulk_create(records[i:i + batch_size], ignore_conflicts=True)
                except Exception as e:
                    return JsonResponse({'message': f'数据上传失败！{str(e)}'}, status=400)
                return JsonResponse({'message': '数据上传成功！'}, status=200)
        except Exception as e:
            return JsonResponse({'error': f'文件处理失败: {str(e)}'}, status=400)
    return JsonResponse({'error': '无效的请求'}, status=400)

@csrf_exempt
def index_searchapi(request):
    # 获取前端传入的查询条件
    school = request.GET.get('school', '')
    score_min = request.GET.get('score_min', '')
    score_max = request.GET.get('score_max', '')
    application_id = request.GET.get('application_id', '')
    tiaojicode = request.GET.get('tiaojicode', '')
    tiaojimubiaoschool=request.GET.get('tiaojimubiaoschool','')
    page = int(request.GET.get('page', 1))  # 默认为第一页
    page_size = int(request.GET.get('page_size', 10))  # 每页显示的记录数

    # 处理空查询参数的情况
    if not school and not score_min and not score_max and not application_id and not tiaojicode:
        return JsonResponse({"success": False, "message": "至少提供一个查询条件"}, status=400)
    # 构造查询条件
    query_filter = Q()
    # 如果字段不为空，才添加对应的过滤条件
    if school and school.strip() != '':
        # 如果字符串是空的也不执行谢谢
        query_filter &= Q(yizhiyuanschool__icontains=school)  # 一志愿学校

    query_filter &= Q(chushiscore__isnull=False)  # 确保不匹配 NULL 值

    if score_min:
        try:
            score_min = int(score_min)  # 将分数转换为整数
            query_filter &= Q(chushiscore__gte=score_min)  # 初试成绩大于等于score_min
        except ValueError:
            return JsonResponse({"success": False, "message": "分数范围格式错误"}, status=400)
    if score_max:
        try:
            score_max = int(score_max)  # 将分数转换为整数
            query_filter &= Q(chushiscore__lte=score_max)  # 初试成绩小于等于score_max
        except ValueError:
            return JsonResponse({"success": False, "message": "分数范围格式错误"}, status=400)
    if application_id:
        application_id = application_id.strip()  # 去掉前后空格
        query_filter &= Q(baokaozhuanyecode__icontains=application_id)  # 调剂id的模糊查询

    if tiaojicode:
        tiaojicode = tiaojicode.strip()  # 去掉前后空格
        # tiaojicode是以空格分割的字符串将其全部获得编号
        tiaojicode_list = tiaojicode.split()
        # 如果列表不为空就执行与操作，比如同时模糊查找0854和0855，如果列表为空就不执行，谢谢
        if tiaojicode_list:
            tiaojicode_query = Q()  # 创建空的 OR 查询
            for code in tiaojicode_list:
                tiaojicode_query |= Q(tiaojizhuanyecode__icontains=code)  # OR 逻辑
            query_filter &= tiaojicode_query  # 仅当列表不为空时才加入主查询条件

    if tiaojimubiaoschool and tiaojimubiaoschool.strip():
        query_filter &= Q(tiaojischool__icontains=tiaojimubiaoschool)  # 仅在有效字符串时执行

  

    queryset = TiaojixinxiD1.objects.filter(query_filter).order_by(
        'tiaojischool', 'tiaojixueyuan', 'tiaojizhuanyename',
        'tiaojizhuanyecode', '-chushiscore'
    )
    # **然后再 `.values()`**
    queryset = queryset.values(
        'name', 'tiaojischool', 'tiaojixueyuan', 'tiaojizhuanyename',
        'tiaojizhuanyecode', 'chushiscore', 'yizhiyuanschool', 'baokaozhuanyecode'
    ).distinct()
    # 计算分页总数
    total_records = queryset.count()
    # 对这些学校采用模糊匹配
    results = queryset[(page - 1) * page_size:page * page_size]  # 分页处
    # 返回数据
    data = {
        "success": True,
        "totalRecords": total_records,
        "results": list(results.values('name','tiaojischool','tiaojixueyuan', 'tiaojizhuanyename', 'tiaojizhuanyecode',
                                       'chushiscore', 'yizhiyuanschool', 'baokaozhuanyecode', 'yearinfo'))
    }
    return JsonResponse(data)


@csrf_exempt
def index_searchapi_demo3(request):
    # 获取前端传入的查询条件
    school = request.GET.get('school', '')
    score_min = request.GET.get('score_min', '')
    score_max = request.GET.get('score_max', '')
    application_id = request.GET.get('application_id', '')
    tiaojicode = request.GET.get('tiaojicode', '')
    xuexiaodangci = request.GET.get('xuexiaodangci', '')
    xuexixingshi = request.GET.get('xuexixingshi','')
    tiaojimubiaoschool = request.GET.get('tiaojimubiaoschool', '')
    page = int(request.GET.get('page', 1))  # 默认为第一页
    page_size = int(request.GET.get('page_size', 10))  # 每页显示的记录数

    # 处理空查询参数的情况
    if not school and not score_min and not score_max and not application_id and not tiaojicode:
        return JsonResponse({"success": False, "message": "至少提供一个查询条件"}, status=400)
    # 构造查询条件
    query_filter = Q()
    # 如果字段不为空，才添加对应的过滤条件
    if school and school.strip() != '':
        # 如果字符串是空的也不执行谢谢
        query_filter &= Q(yizhiyuanschool__icontains=school)  # 一志愿学校
    if score_min:
        try:
            score_min = int(score_min)  # 将分数转换为整数
            query_filter &= Q(chushiscore__gte=score_min)  # 初试成绩大于等于score_min
        except ValueError:
            return JsonResponse({"success": False, "message": "分数范围格式错误"}, status=400)
    if score_max:
        try:
            score_max = int(score_max)  # 将分数转换为整数
            query_filter &= Q(chushiscore__lte=score_max)  # 初试成绩小于等于score_max
        except ValueError:
            return JsonResponse({"success": False, "message": "分数范围格式错误"}, status=400)
    if application_id:
        application_id = application_id.strip()  # 去掉前后空格
        query_filter &= Q(yihziyuancode__icontains=application_id)  # 调剂id的模糊查询

    if tiaojicode:
        tiaojicode = tiaojicode.strip()  # 去掉前后空格
        # tiaojicode是以空格分割的字符串将其全部获得编号
        tiaojicode_list = tiaojicode.split()
        # 如果列表不为空就执行与操作，比如同时模糊查找0854和0855，如果列表为空就不执行，谢谢
        if tiaojicode_list:
            tiaojicode_query = Q()  # 创建空的 OR 查询
            for code in tiaojicode_list:
                tiaojicode_query |= Q(tiaojizhuanyecode__icontains=code)  # OR 逻辑
            query_filter &= tiaojicode_query  # 加入主查询条件

    if xuexiaodangci and xuexiaodangci.strip() != '':
        xuexiaodangci = xuexiaodangci.strip()  # 去掉前后空格
        query_filter &= Q(tiaojidangci__exact=xuexiaodangci)  # 调剂id的模糊查询

    if xuexixingshi and xuexixingshi.strip() != '':
        xuexixingshi = xuexixingshi.strip()  # 去掉前后空格
        query_filter &= Q(xuexixingshi__exact=xuexixingshi)  # 精确查询

    if tiaojimubiaoschool and tiaojimubiaoschool.strip() != '':
        # 如果字符串是空的也不执行谢谢
        print(tiaojimubiaoschool)
        query_filter &= Q(tiaojischool__icontains=tiaojimubiaoschool)  # 调剂目标学校
    # 隐藏的学校,如果大数据推荐的学校中包含如下内容则不进行显示。
    hide_schools = \
        ['西安理工大学', '上海工程技术大学', '嘉兴大学', '上海电力大学','(10856)上海工程技术大学','(10354)嘉兴大学',
                        '(3106)上海第二工业大学', '西安工程大学', '上海海事大学', '天津城建大学','(10256)上海电力大学',
                        '天津职业技术师范大学', '(10066)天津职业技术师范大学','西安石油大学','(10709)西安工程大学','(10108)山西大学',
                        '天津中德应用技术大学','(12105)天津中德应用技术大学','太原理工大学','(10475)河南大学','(10112)太原理工大学','(10755)新疆大学',
         '河南大学','景德镇陶瓷大学','浙江科技大学','(10408)景德镇陶瓷大学','(10254)上海海事大学','(10792)天津城建大学','(10677)西南林业大学',
         '(11057)浙江科技大学']
    if hide_schools:
        query_filter &= ~reduce(or_, [Q(tiaojischool=name) for name in hide_schools])

    print(f"查询条件: {query_filter}")
    # 去重如果tiaojischool、tiaojixueyuan、tiaojizhuanye、tiaojizhuanyecode、yizhiyuanschool、yihziyuancode、chushiscore相同则认为是重复的，需要去重

    from django.db.models import Max

    queryset = (
        TiaojixinxiD3.objects
        .filter(query_filter)
        .values('tiaojischool', 'tiaojixueyuan', 'tiaojizhuanye', 'tiaojizhuanyecode', 'yizhiyuanschool',
                'yihziyuancode')
        .annotate(max_chushiscore=Max('chushiscore'))
        .order_by('tiaojischool', 'tiaojixueyuan', 'tiaojizhuanye', 'tiaojizhuanyecode', '-max_chushiscore')
    )

    '''
         queryset = TiaojixinxiD3.objects.filter(query_filter).order_by('tiaojischool', 'tiaojixueyuan', 'tiaojizhuanye',
                                                                   'tiaojizhuanyecode',
                                                                   '-chushiscore')
        'tiaojischool' → 先按照 调剂学校 升序 排序
        'tiaojixueyuan' → 在同一学校内，按照 调剂学院 升序 排序
        'tiaojizhuanyecode' → 在同一学院内，按照 调剂专业代码 升序 排序
        '-chushiscore' → 在相同专业下，按照 初试分数 降序 排序（高分在前）
          
    '''
    total_records = queryset.count()  # 获取记录总数
    results = queryset[(page - 1) * page_size:page * page_size]  # 分页处
    # 返回数据
    data = {
        "success": True,
        "totalRecords": total_records,
        "results": list(results.values('tiaojischool', 'tiaojiaddress', 'tiaojidangci'
                                       ,'tiaojixueyuan','tiaojizhuanye','tiaojizhuanyecode',
                                       'xuexixingshi','chushiscore','yizhiyuanschool','yihziyuancode','yearinfo'))
    }
    return JsonResponse(data)


@csrf_exempt
def index_searchapi_demo4(request):
    # 获取前端传入的查询条件
    school = request.GET.get('school', '')
    score_min = request.GET.get('score_min', '')
    score_max = request.GET.get('score_max', '')
    application_id = request.GET.get('application_id', '')
    tiaojicode = request.GET.get('tiaojicode', '')
    xuexiaodangci = request.GET.get('xuexiaodangci', '')
    xuexixingshi = request.GET.get('xuexixingshi', '')
    tiaojimubiaoschool = request.GET.get('tiaojimubiaoschool', '')
    tiaojimubiaoaddress = request.GET.get('tiaojimubiaoaddress', '')
    page = int(request.GET.get('page', 1))  # 默认为第一页
    page_size = int(request.GET.get('page_size', 10))  # 每页显示的记录数

    # 处理空查询参数的情况
    if not school and not score_min and not score_max and not application_id and not tiaojicode:
        return JsonResponse({"success": False, "message": "至少提供一个查询条件"}, status=400)
    # 构造查询条件
    query_filter = Q()
    # 如果字段不为空，才添加对应的过滤条件
    if school and school.strip() != '':
        # 如果字符串是空的也不执行谢谢
        query_filter &= Q(yizhiyuanschool__icontains=school)  # 一志愿学校
    if score_min:
        try:
            score_min = int(score_min)  # 将分数转换为整数
            query_filter &= Q(chushiscore__gte=score_min)  # 初试成绩大于等于score_min
        except ValueError:
            return JsonResponse({"success": False, "message": "分数范围格式错误"}, status=400)
    if score_max:
        try:
            score_max = int(score_max)  # 将分数转换为整数
            query_filter &= Q(chushiscore__lte=score_max)  # 初试成绩小于等于score_max
        except ValueError:
            return JsonResponse({"success": False, "message": "分数范围格式错误"}, status=400)
    if application_id:
        application_id = application_id.strip()  # 去掉前后空格
        query_filter &= Q(yihziyuancode__icontains=application_id)  # 调剂id的模糊查询

    if tiaojicode:
        tiaojicode = tiaojicode.strip()  # 去掉前后空格
        # tiaojicode是以空格分割的字符串将其全部获得编号
        tiaojicode_list = tiaojicode.split()
        # 如果列表不为空就执行与操作，比如同时模糊查找0854和0855，如果列表为空就不执行，谢谢
        if tiaojicode_list:
            tiaojicode_query = Q()  # 创建空的 OR 查询
            for code in tiaojicode_list:
                tiaojicode_query |= Q(tiaojizhuanyecode__icontains=code)  # OR 逻辑
            query_filter &= tiaojicode_query  # 加入主查询条件

    if xuexiaodangci and xuexiaodangci.strip() != '':
        xuexiaodangci = xuexiaodangci.strip()  # 去掉前后空格
        query_filter &= Q(tiaojidangci__exact=xuexiaodangci)  # 调剂id的模糊查询

    if xuexixingshi and xuexixingshi.strip() != '':
        xuexixingshi = xuexixingshi.strip()  # 去掉前后空格
        query_filter &= Q(xuexixingshi__exact=xuexixingshi)  # 精确查询

    if tiaojimubiaoschool and tiaojimubiaoschool.strip() != '':
        # 如果字符串是空的也不执行谢谢
        print(tiaojimubiaoschool)
        query_filter &= Q(tiaojischool__icontains=tiaojimubiaoschool)  # 调剂目标学校
    if tiaojimubiaoaddress and tiaojimubiaoaddress.strip() != '':
        # 如果字符串是空的也不执行谢谢
        print(tiaojimubiaoaddress)
        query_filter &= Q(tiaojiaddress__icontains=tiaojimubiaoaddress)  # 调剂目标地区
    # 隐藏的学校,如果大数据推荐的学校中包含如下内容则不进行显示。
    hide_schools = \
        []
    if hide_schools:
        query_filter &= ~reduce(or_, [Q(tiaojischool=name) for name in hide_schools])

    print(f"查询条件: {query_filter}")
    # 去重如果tiaojischool、tiaojixueyuan、tiaojizhuanye、tiaojizhuanyecode、yizhiyuanschool、yihziyuancode、chushiscore相同则认为是重复的，需要去重

    from django.db.models import Max

    queryset = (
        TiaojixinxiD3.objects
        .filter(query_filter)
        .values('tiaojischool', 'tiaojixueyuan', 'tiaojizhuanye', 'tiaojizhuanyecode', 'yizhiyuanschool',
                'yihziyuancode')
        .annotate(max_chushiscore=Max('chushiscore'))
        .order_by('tiaojischool', 'tiaojixueyuan', 'tiaojizhuanye', 'tiaojizhuanyecode', '-max_chushiscore')
    )
    total_records = queryset.count()  # 获取记录总数
    results = queryset[(page - 1) * page_size:page * page_size]  # 分页处
    # 返回数据
    data = {
        "success": True,
        "totalRecords": total_records,
        "results": list(results.values('tiaojischool', 'tiaojiaddress', 'tiaojidangci'
                                       , 'tiaojixueyuan', 'tiaojizhuanye', 'tiaojizhuanyecode',
                                       'xuexixingshi', 'chushiscore', 'yizhiyuanschool', 'yihziyuancode', 'yearinfo'))
    }
    return JsonResponse(data)
